Release 10.1A: OpenEdge Data Management:
SQL Development

GRANT statement

The GRANT statement can be used to provide the user with database-wide or table-specific privileges. It can also grant public access to restricted columns.

Database-wide privileges

Database-wide privileges grant the user DBA, RESOURCE, AUDIT_ADMIN, AUDIT_ARCHIVE, or AUDIT_INSERT privileges. Users with DBA privileges have the ability to access, modify, or delete a database object and to grant privileges to other users. RESOURCE privileges allow a user to create database objects. Users with AUDIT_ADMIN privileges can read the data of audit-enabled databases. Users with AUDIT_ARCHIVE privileges can read, archive, and delete audit data. Users with AUDIT_INSERT privileges can insert application audit events into audit tables. For general information about implementing auditing, see OpenEdge Getting Started: Core Business Services .

The GRANT statement syntax for granting RESOURCE or DBA privileges is:

Syntax

GRANT { RESOURCE, DBA, AUDIT_ADMIN, AUDIT_ARCHIVE, AUDIT_INSERT } TO username [, username ], ... [ WITH GRANT OPTION ];

Note: By employing the WITH GRANT OPTION clause, you enable a user to grant the same privilege he or she has been granted to others. This clause should be used carefully due to its ability to affect database security.

Example 4–3 demonstrates the use of the GRANT RESOURCE statement.

Example 4–3: GRANT RESOURCE statement

GRANT RESOURCE TO 'GSP';

In this case, GSP is granted the privilege to issue CREATE statements, and can therefore add objects, such as tables, to the database.

Table-specific privileges

Table-specific privileges can be granted to users so they can view, add, delete, or create indexes for data within a table. Privileges can also be granted to allow users to refer to a table from another table’s constraint definitions.

The GRANT statement syntax for granting table-specific privileges is:

Syntax

GRANT { privilege [, privilege ], ... | ALL } ON table_name TO { username [, username ], ... | PUBLIC } [ WITH GRANT OPTION ] ;

This is the syntax for the privilege value:

Syntax

{ SELECT | INSERT | DELETE | INDEX    | UPDATE [ ( column , column , ... ) ]    | REFERENCES [ ( column , column , ... ) ] }

In this instance, a DBA restricts the types of activities a user is allowed to perform on a table. In Example 4–4, 'GSP' is given permission to update the item name, item number, and catalog descriptions found in the item table.

Note: By employing the WITH GRANT OPTION clause, you enable a user to grant the same privilege he or she has been granted to others. This clause should be used carefully due to its ability to affect database security.

Example 4–4 illustrates the granting of table-specific privileges.

Example 4–4: GRANT UPDATE statement

GRANT UPDATE ON Item (ItemNum, ItemName, CatDescription) TO 'GSP';

The GRANT UPDATE statement has limited GSP’s ability to interact with the item table. Now, if GSP attempts to update a column to which he has not been granted access, the database will return the error message in Example 4–5.

Example 4–5: SQL error message

=== SQL Exception 1 === SQLState=HY000 ErrorCode=-20228 [JDBC Progress Driver}:Access Denied (Authorisation failed) (7512)

Granting public access

The GRANT statement can be easily modified to make previously restricted columns accessible to the public, as in Example 4–6.

Example 4–6: Granting update privilege to public

GRANT UPDATE ON Item (ItemNum, ItemName, CatDescription) TO PUBLIC;

For detailed information on the GRANT statement, see OpenEdge Data Management: SQL Reference .